Unit BSQLBuilderUnit;

Interface

Uses
  sysutils, BCommonUnit;

Type BFieldKind = (
  fkInteger = 0,
  fkDouble = 1,
  fkString = 2,
  fkDate = 3,
  fkTime = 4,
  fkTimeStamp = 5);

Type BWhereKind = (
  wkAnd = 0,
  wkOr = 1);

Type

{ BSQLBuilderClass }

 BSQLBuilderClass = Class
  Private
    bAction: String;
    bExecutableQuery: Boolean;
    bText: String;
    bTable: String;
    bFields: String;
    bParams: String;
    bGenerators: String;
    bReturning: String;
    bConstraints: String;
    bWhere: String;
    bOrder: String;
    Function GetText: String;
  Public
    Property Text: String Read GetText;
    Property ExecutableQuery: Boolean Read bExecutableQuery;

    Function Select(Const aTableName: String): BSQLBuilderClass; Inline;
    Function Insert(Const aTableName: String): BSQLBuilderClass; Inline;
    Function Insert(Const aTableNameMask: String;
      Const aParams: Array Of Const): BSQLBuilderClass; Inline;
    Function Update(Const aTableName: String): BSQLBuilderClass; Inline;
    Function UpdateOrInsert(Const aTableName: String): BSQLBuilderClass; Inline;
    Function Delete(Const aTableName: String): BSQLBuilderClass;Inline;

    Function CreateGenerator(Const aGenName: String): BSQLBuilderClass;
    Function CreateTable(Const aTableName: String): BSQLBuilderClass;
    Function CreateTable(Const aTableNameMask: String;
      Const aParams: Array Of Const): BSQLBuilderClass;

    Function AlterTable(Const aTableName: String): BSQLBuilderClass;
    Function AlterTable(Const aTableNameMask: String;
      Const aParams: Array Of Const): BSQLBuilderClass;

    Function All: BSQLBuilderClass;

    Function AddField(Const aName: String): BSQLBuilderClass; Inline;
    Function AddField(Const aName: String; Const aKind: BFieldKind;
      Const aNotNull: Boolean = TRUE): BSQLBuilderClass; Inline;
    Function AddField(Const aNameMask: String; Const aKind: BFieldKind;
      Const aNotNull: Boolean;
      Const aParams: Array Of Const): BSQLBuilderClass; Inline;

    Function AddStringField(Const aName: String;
      Const aNotNull: Boolean = TRUE): BSQLBuilderClass; Inline;
    Function AddStringField(Const aNameMask: String;
      Const aParams: Array Of Const;
      Const aNotNull: Boolean = TRUE): BSQLBuilderClass; Inline;
    Function AddIntegerField(Const aName: String;
      Const aNotNull: Boolean = TRUE): BSQLBuilderClass; Inline;
    Function AddIntegerField(Const aNameMask: String;
      Const aParams: Array Of Const;
      Const aNotNull: Boolean = TRUE): BSQLBuilderClass; Inline;
    Function AddIntegerFields(Const aNames: Array Of String;
      Const aNotNull: Boolean = TRUE): BSQLBuilderClass; Inline;
    Function AddDoubleField(Const aName: String;
      aNotNull: Boolean = TRUE): BSQLBuilderClass; Inline;
    Function AddDoubleField(Const aNameMask: String;
      Const aParams: Array Of Const;
      aNotNull: Boolean = TRUE): BSQLBuilderClass; Inline;
    Function AddDateField(Const aName: String;
      Const aNotNull: Boolean = TRUE): BSQLBuilderClass; Inline;
    Function AddDateField(Const aNameMask: String;
      Const aParams: Array Of Const;
      Const aNotNull: Boolean = TRUE): BSQLBuilderClass; Inline;
    Function AddGenerator(Const aName: String = '';
      Const aCount: Integer = 1): BSQLBuilderClass; Inline;

    Function DropField(Const aField: String): BSQLBuilderClass; Inline;
    Function DropField(Const aFieldMask: String;
      Const aParams: Array Of Const): BSQLBuilderClass;

    Function AddParam(Const aParam: String): BSQLBuilderClass; Inline;
    Function AddParam(Const aParamMask: String;
      Const aParams: Array Of Const): BSQLBuilderClass; Inline;
    Function AddParams(Const aParams: Array Of String): BSQLBuilderClass;

    Function Returning(Const aField: String): BSQLBuilderClass; Inline;

    Function AddPrimaryKey(Const aField: String;
      Const aDesc: Boolean = FALSE): BSQLBuilderClass; Inline;

    Function Where(Const aStatement: String; Const aKind: BWhereKind = wkAnd;
      Const aNegative: Boolean = FALSE): BSQLBuilderClass; Inline;
    Function Where(Const aStatementMask: String; Const aParams: Array Of Const;
      Const aKind: BWhereKind = wkAnd;
      Const aNegative: Boolean = FALSE): BSQLBuilderClass; Inline;

    Function OrderBy(Const aField: String): BSQLBuilderClass; Inline;
    Function OrderBy(Const aFieldMask: String;
      Const aParams: Array Of Const): BSQLBuilderClass; Inline;

    Function Executable: BSQLBuilderClass;

    Procedure Clear;
    Constructor Build;
    Destructor Burn;
End;

Implementation

Function ReturnFieldType(Const aKind: BFieldKind): String;
Begin
  Result := '';
  Case aKind Of
    fkInteger: Result := 'INTEGER';
    fkDouble: Result := 'DOUBLE PRECISION';
    fkString: Result := 'VARCHAR(1024)';
    fkDate: Result := 'DATE';
    fkTime: Result := 'TIME';
    fkTimeStamp: Result := 'TIMESTAMP';
  End;
  If Result = '' Then
    Raise BExceptionClass.Build('Illegal Kind of Field');
End;

{ BSQLBuilderClass }

Function BSQLBuilderClass.GetText: String;
Begin
  If bAction = 'SELECT ' Then
    Begin
      Result := bAction +  bFields + 'FROM ' + bTable;
      If Not(bWhere = '') Then Result += ' WHERE ' + bWhere;
      If Not(bOrder = '') Then Result += ' ORDER BY ' + bOrder;
    End;

  If bAction = 'ALTER TABLE ' Then
    Begin
      Result := bAction + bTable + ' ' + bFields;
    End;

  If (bAction = 'INSERT INTO ') Or (bAction = 'UPDATE OR INSERT INTO ') Then
    Begin
      Result := bAction + bTable + '(' + bFields + ') VALUES('+bGenerators;
      If Not(bGenerators = '') Then Result += ',';
      Result += bParams;
      Result += ')';
      If Not(bReturning = '') Then Result += ' RETURNING ' + bReturning;
    End;

  If (bAction = 'UPDATE ') Then
    Begin
      Result := bAction + bTable + Format(' SET %s', [bParams]);
      If Not(bWhere = '') Then Result += ' WHERE ' + bWhere;
    End;

  If (bAction = 'DELETE FROM ') Then
    Result := bAction + bTable + ' WHERE ' + bWhere;

  If (bAction = 'CREATE TABLE ') Then
    Result := bAction + bTable + '(' + bFields + bConstraints + ')';

  If Result = '' Then
    Result := bAction + bTable;
end;

Function BSQLBuilderClass.Select(Const aTableName: String): BSQLBuilderClass;
Begin
  Result := Self;
  bAction := 'SELECT ';
  bTable := aTableName;
End;

Function BSQLBuilderClass.Insert(Const aTableName: String): BSQLBuilderClass;
Begin
  Result := Self;
  bAction := 'INSERT INTO ';
  bTable := aTableName;
End;

Function BSQLBuilderClass.Insert(Const aTableNameMask: String;
  Const aParams: Array Of Const): BSQLBuilderClass;
Begin
  Result := Insert(Format(aTableNameMask, aParams));
End;

Function BSQLBuilderClass.Update(Const aTableName: String): BSQLBuilderClass;
Begin
  Result := Self;
  bAction := 'UPDATE ';
  bTable := aTableName;
End;

Function BSQLBuilderClass.UpdateOrInsert(
  Const aTableName: String): BSQLBuilderClass;
Begin
  Result := Self;
  bAction := 'UPDATE OR INSERT INTO ';
  bTable := aTableName;
End;

Function BSQLBuilderClass.Delete(Const aTableName: String): BSQLBuilderClass;
Begin
  Result := Self;
  bAction := 'DELETE FROM ';
  bTable := aTableName;
End;

Function BSQLBuilderClass.CreateGenerator(Const aGenName: String
  ): BSQLBuilderClass;
Begin
  Result := Self;
  bTable := aGenName;
  bAction := 'CREATE GENERATOR ';
End;

Function BSQLBuilderClass.All: BSQLBuilderClass;
Begin
  Result := Self;
  bFields := '* ';
End;

Function BSQLBuilderClass.AddField(Const aName: String): BSQLBuilderClass;
Begin
  Result := Self;
  AppendCommaString(bFields, aName);
End;

Function BSQLBuilderClass.CreateTable(
  Const aTableName: String): BSQLBuilderClass;
Begin
  Result := Self;
  bTable := aTableName;
  bAction := 'CREATE TABLE ';
End;

Function BSQLBuilderClass.CreateTable(Const aTableNameMask: String;
  Const aParams: Array Of Const): BSQLBuilderClass;
Begin
  Result := CreateTable(Format(aTableNameMask, aParams));
End;

Function BSQLBuilderClass.AlterTable(
  Const aTableName: String): BSQLBuilderClass;
Begin
  Result := Self;
  bAction := 'ALTER TABLE ';
  bTable := aTableName;
End;

Function BSQLBuilderClass.AlterTable(Const aTableNameMask: String;
  Const aParams: Array Of Const): BSQLBuilderClass;
Begin
  Result := AlterTable(Format(aTableNameMask, aParams));
End;

Function BSQLBuilderClass.AddField(Const aName: String;
  Const aKind: BFieldKind; Const aNotNull: Boolean): BSQLBuilderClass;
Begin
  Result := Self;
  If bAction = 'ALTER TABLE ' Then
    AppendCommaString(bFields, 'ADD %s %s ', [aName, ReturnFieldType(aKind)])
  Else
    AppendCommaString(bFields, '%s %s ', [aName, ReturnFieldType(aKind)]);
  If aNotNull Then bFields += 'NOT NULL ';
End;

Function BSQLBuilderClass.AddField(Const aNameMask: String;
  Const aKind: BFieldKind; Const aNotNull: Boolean;
  Const aParams: Array Of Const): BSQLBuilderClass;
Begin
  Result := AddField(Format(aNameMask, aParams), aKind, aNotNull);
End;

Function BSQLBuilderClass.AddIntegerField(Const aName: String;
  Const aNotNull: Boolean): BSQLBuilderClass;
Begin
  Result := AddField(aName, fkInteger, aNotNull);
End;

Function BSQLBuilderClass.AddIntegerField(Const aNameMask: String;
  Const aParams: Array Of Const; Const aNotNull: Boolean): BSQLBuilderClass;
Begin
  Result := AddIntegerField(Format(aNameMask, aParams), aNotNull);
End;

Function BSQLBuilderClass.AddIntegerFields(Const aNames: Array Of String;
  Const aNotNull: Boolean): BSQLBuilderClass;
Var
  i: Integer;
Begin
  Result := Self;
  For i := Low(aNames) To High(aNames) Do
    AddIntegerField(aNames[i], aNotNull);
End;

Function BSQLBuilderClass.AddStringField(Const aName: String;
  Const aNotNull: Boolean): BSQLBuilderClass;
Begin
  Result := AddField(aName, fkString, aNotNull);
End;

Function BSQLBuilderClass.AddStringField(Const aNameMask: String;
  Const aParams: Array Of Const; Const aNotNull: Boolean): BSQLBuilderClass;
Begin
  Result := AddStringField(Format(aNameMask, aParams), aNotNull);
End;

Function BSQLBuilderClass.AddDateField(Const aName: String;
  Const aNotNull: Boolean): BSQLBuilderClass;
Begin
  Result := AddField(aName, fkDate, aNotNull);
End;

Function BSQLBuilderClass.AddDateField(Const aNameMask: String;
  Const aParams: Array Of Const; Const aNotNull: Boolean): BSQLBuilderClass;
Begin
  Result := AddDateField(Format(aNameMask, aParams), aNotNull);
End;

Function BSQLBuilderClass.AddDoubleField(Const aName: String;
  aNotNull: Boolean): BSQLBuilderClass;
Begin
  Result := AddField(aName, fkDouble, aNotNull);
End;

Function BSQLBuilderClass.AddDoubleField(Const aNameMask: String;
  Const aParams: Array Of Const; aNotNull: Boolean): BSQLBuilderClass;
Begin
  Result := AddDoubleField(Format(aNameMask, aParams), aNotNull);
End;

Function BSQLBuilderClass.AddGenerator(Const aName: String;
  Const aCount: Integer): BSQLBuilderClass;
Begin
  Result :=Self;
  If aName = '' Then
    Begin
      AppendCommaString(bGenerators, 'GEN_ID(GEN_%s_ID, %d)', [bTable, aCount]);
      AppendCommaString(bFields, 'ID');
    End
  Else
    Begin
      AppendCommaString(bGenerators, 'GEN_ID(GEN_%s_%s, %d) ',
        [bTable, aName, aCount]);
      AppendCommaString(bFields, aName);
    End;
End;

Function BSQLBuilderClass.DropField(Const aField: String): BSQLBuilderClass;
Begin
  Result := AddField('DROP ' + aField);
End;

Function BSQLBuilderClass.DropField(Const aFieldMask: String;
  Const aParams: Array Of Const): BSQLBuilderClass;
Begin
  Result := DropField(Format(aFieldMask, aParams));
End;

Function BSQLBuilderClass.AddParam(Const aParam: String): BSQLBuilderClass;
Begin
  Result := Self;
  AppendCommaString(bFields, aParam);
  If bAction = 'UPDATE ' Then
    AppendCommaString(bParams, Format('%0:s = :%0:s', [aParam]))
  Else
    AppendCommaString(bParams, ':' + aParam);
End;

Function BSQLBuilderClass.AddParam(Const aParamMask: String;
  Const aParams: Array Of Const): BSQLBuilderClass;
Begin
  Result := AddParam(Format(aParamMask, aParams));
End;

Function BSQLBuilderClass.AddParams(
  Const aParams: Array Of String): BSQLBuilderClass;
Var
  i: Integer;
Begin
  Result := Self;
  For i := Low(aParams) To High(aParams) Do AddParam(aParams[i]);
End;

Function BSQLBuilderClass.Returning(Const aField: String): BSQLBuilderClass;
Begin
  Result := Self;
  bReturning := aField;
End;

Function BSQLBuilderClass.AddPrimaryKey(Const aField: String;
  Const aDesc: Boolean): BSQLBuilderClass;
Begin
  Result := Self;
  If aDesc Then
    bConstraints += Format(', CONSTRAINT PK_%0:s_%1:s PRIMARY KEY(%1:s) ' +
      'USING DESCENDING INDEX PK_%0:s_%1:s', [bTable, aField])
  Else
    bConstraints += Format(', CONSTRAINT PK_%0:s_%1:s PRIMARY KEY(%1:s)',
      [bTable, aField]);
End;

Function BSQLBuilderClass.Where(Const aStatement: String;
  Const aKind: BWhereKind; Const aNegative: Boolean): BSQLBuilderClass;
Begin
  Result := Self;
  If Not(bWhere = '') Then
    Case aKind Of
      wkAnd: bWhere += 'AND ';
      wkOr: bWhere += 'OR ';
    End;
  If aNegative Then bWhere += 'NOT';
  bWhere +=  '(' + aStatement + ') ';
End;

Function BSQLBuilderClass.Where(Const aStatementMask: String;
  Const aParams: Array Of Const; Const aKind: BWhereKind;
  Const aNegative: Boolean): BSQLBuilderClass;
Begin
  Result := Where(Format(aStatementMask, aParams), aKind, aNegative);
End;

Function BSQLBuilderClass.OrderBy(Const aField: String): BSQLBuilderClass;
Begin
  Result := Self;
  AppendCommaString(bOrder, aField + ' ');
End;

Function BSQLBuilderClass.OrderBy(Const aFieldMask: String;
  Const aParams: Array Of Const): BSQLBuilderClass;
Begin
  Result := OrderBy(Format(aFieldMask, aParams));
End;

Function BSQLBuilderClass.Executable: BSQLBuilderClass;
Begin
  Result := Self;
  bExecutableQuery := TRUE;
End;

Procedure BSQLBuilderClass.Clear;
Begin
  bExecutableQuery := FALSE;
  bAction := '';
  bText := '';
  bTable := '';
  bFields := '';
  bReturning := '';
  bConstraints := '';
  bOrder := '';
  bWhere := '';
  bParams := '';
  bGenerators := '';
End;

Constructor BSQLBuilderClass.Build;
Begin

End;

Destructor BSQLBuilderClass.Burn;
Begin

End;

End.
